package com.bdqn.demo.dao;

import com.bdqn.demo.pojo.SysUser;
import com.bdqn.demo.pojo.SysUserVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * SysUserRepository
 *
 * @author f
 * @since 2021/11/23
 */
public interface SysUserRepository extends JpaRepository<SysUser,Long>, JpaSpecificationExecutor<SysUser> {

        List<SysUser> findByUsrName(String usrName);

        List<SysUser> findByUsrNameAndUsrPasswordAndUsrRoleIdOrderByUsrId(String usrName,String usrPassword,Long usrRoleId);

        @Query(value = "select * from sys_user where usr_role_id=?1", nativeQuery = true)
        List<SysUser> findByRoleIdNative(Long roleId);

        // 此方法对应SysUser实体中的命名查询，定义方法为: @NamedQueries(@NamedQuery(name = "SysUser.findUsersByName"))
        // 如果希望根据接口方法名来实现数据库操作，则应命名为: findUsersByUsrName
        List<SysUser> findUsersByName(String usrName);

        // @Query("select u from SysUser u where u.usrRoleId=?1")
        // Page<SysUser> findPageByUsrRoleId(Long roleId, Pageable pageable);

        @Query("select u from SysUser u where u.usrRoleId=:roleId")
        List<SysUser> findByRoleIdParam(@Param("roleId") Long roleId);

        // @Transactional(timeout = 10)
        // @Modifying
        // @Query("update SysUser u set u.usrName=?1 where u.usrId=?2")
        // int modifyNameById(String usrName, Long usrId);

        @Query("select u.usrId as usrId, u.usrName as usrName, u.usrPassword as usrPassword, u.usrRoleId as usrRoleId, u.usrFlag as usrFlag, r.roleName as roleName from SysUser u, SysRole r where u.usrRoleId=r.roleId and u.usrId=?1")
        SysUserVo getSysUserVo(Long usrId);

        public List<SysUser> findByUsrNameAndUsrPassword(String usrName,String usrPassword);

        public Long countByUsrName(String usrName);

        public List<SysUser> findByUsrNameLike(String usrName);

        // @Query(value = "select u from SysUser u where u.usrRoleId=?1",nativeQuery = true)
        // public List<SysUser> findByUsrRoleId(Long roleId);

        @Query("select u from SysUser u where u.usrRoleId=:roleId")
        public List<SysUser> findByUsrRoleId(@Param("usrroleId") Long roleId);

        @Transactional(timeout = 10)
        @Modifying
        @Query("update SysUser u set u.usrName=?1 where u.usrId=?2")
        public int modifyNameById(String usrName,Long usrId);

        // 命名 QL 查询
        public List<SysUser> findUsersByUsrName(String usrName);

        // 分页查询
        public Page<SysUser> findPageByUsrRoleId(Long roleId, Pageable pageable);

        @Query("select u.usrId as usrId,u.usrName as usrName,u.usrPassword as usrPassword,u.usrRoleId as usrRoleId,"
        + "u.usrFlag as usrFlag,r.roleName as roleName from SysUser u,SysRole r where u.usrRoleId=r.roleId and u.usrId=?1")
        public UserInfo getSysUserByInfo(Long usrId);

        @Query("select u from SysUser u where u.usrRoleId=?1")
        List<SysUser> findByRoleId(Long roleId);
}
